/* Treasury.do (STATA)
	Construct US Treasury data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using Treasury, replace;

/* Define local and global variables */

local directory = "../../Data/US Treasury";

do global;


/* Step 1: Construct debt data */

/* Load LT debt data */

tempfile Debt;

/* 2003-2006 */

forval y = $year_min/2006 {;
	if `y'==$year_min {;
		local file = "Table 18";
	};
	else {;
		local file = "Table 21";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/`file'",
		first allstring clear;

	/* Rename variables */

	rename Countryorregion Name;
	rename US amountUSA;
	rename Euro amountEUR;
	rename JapaneseYen amountJPN;
	rename UKPound amountGBR;
	cap rename OwnCurrency amountOWN;
	cap rename Other amount_OC;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 2;

	/* Save data */

	if `y'!=$year_min	append using `Debt';

	save `Debt', replace;
};

/* 2007-2012 */

forval y = 2007/2012 {;
	if `y'==2007 {;
		local sheet = "Table 24";
		local cell = "A4";
	};
	else if `y'==2008 {;
		local sheet = "TB24";
		local cell = "A5";
	};
	else {;
		local sheet = "A6";
		local cell = "A5";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/shc`y'r-appx",
		sheet("`sheet'") cellra(`cell') first allstring clear;

	/* Rename variables */

	rename Countryorregion Name;
	rename US amountUSA;
	rename Euro amountEUR;
	rename JapaneseYen amountJPN;
	rename UKPound amountGBR;
	rename OwnCurrency amountOWN;

	keep Name-amountOWN;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 2;

	/* Save data */

	append using `Debt';

	save `Debt', replace;
};

/* 2013-2020 */

forval y = 2013/$year_max {;
	if `y'==2013 {;
		local file = "appendix_tab06";
	};
	else if `y'==2014 {;
		local file = "shc14_app06";
	};
	else if `y'==2015 {;
		local file = "shc15_app06";
	};
	else {;
		local file = "shc_app06_`y'";
	};

	/* Load data */	

	import delimited using "`directory'/shca`y'/`file'",
		varn(5) case(preserve) rowr(6) stringc(_all) clear;

	/* Rename variables */

	rename Countryorregionofissuer Name;
	cap rename US amountUSA;
	cap rename USdollar amountUSA;
	rename Euro amountEUR;
	cap rename Yen amountJPN;
	cap rename yen amountJPN;
	cap rename Pound amountGBR;
	cap rename pound amountGBR;
	cap rename Currency amountOWN;
	cap rename currency amountOWN;

	keep Name-amountOWN;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 2;

	/* Save data */

	append using `Debt';

	save `Debt', replace;
};

/* Load ST debt data */

/* 2003-2006 */

forval y = $year_min/2006 {;
	if `y'==$year_min {;
		local file = "Table 19";
	};
	else {;
		local file = "Table 22";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/`file'",
		first allstring clear;

	/* Rename variables */

	rename Countryorregion Name;
	rename US amountUSA;
	rename Euro amountEUR;
	rename JapaneseYen amountJPN;
	rename UKPound amountGBR;
	cap rename OwnCurrency amountOWN;
	cap rename Other amount_OC;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 1;

	/* Save data */

	append using `Debt';

	save `Debt', replace;
};

/* 2007-2012 */

forval y = 2007/2012 {;
	if `y'==2007 {;
		local sheet = "Table 25";
		local cell = "A4";
	};
	else if `y'==2008 {;
		local sheet = "TB25";
		local cell = "A5";
	};
	else {;
		local sheet = "A7";
		local cell = "A5";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/shc`y'r-appx",
		sheet("`sheet'") cellra(`cell') first allstring clear;

	/* Rename variables */

	rename Countryorregion Name;
	rename US amountUSA;
	rename Euro amountEUR;
	rename JapaneseYen amountJPN;
	rename UKPound amountGBR;
	rename OwnCurrency amountOWN;

	keep Name-amountOWN;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 1;

	/* Save data */

	append using `Debt';

	save `Debt', replace;
};

/* 2013-2020 */

forval y = 2013/$year_max {;
	if `y'==2013 {;
		local file = "appendix_tab07";
	};
	else if `y'==2014 {;
		local file = "shc14_app07";
	};
	else if `y'==2015 {;
		local file = "shc15_app07";
	};
	else {;
		local file = "shc_app07_`y'";
	};

	/* Load data */	

	import delimited using "`directory'/shca`y'/`file'",
		varn(5) case(preserve) rowr(6) stringc(_all) clear;

	/* Rename variables */

	rename Countryorregionofissuer Name;
	cap rename US amountUSA;
	cap rename USdollar amountUSA;
	rename Euro amountEUR;
	cap rename Yen amountJPN;
	cap rename yen amountJPN;
	cap rename Pound amountGBR;
	cap rename pound amountGBR;
	cap rename Currency amountOWN;
	cap rename currency amountOWN;

	keep Name-amountOWN;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(Total);

	drop total;

	/* Construct variables */

	gen int year = `y';
	gen byte type = 1;

	/* Save data */

	append using `Debt';

	save `Debt', replace;
};

/* Replace small holdings */

foreach var of varlist Total amount* {;
	replace `var' = "0" if inlist(strtrim(`var'),"*","n.a.");
};

destring Total amount*, replace;

/* Fix variables */

egen foreign = rowtotal(amountUSA amountEUR amountJPN amountGBR);

replace amountOWN = max(Total-foreign,0) if year<=2005;

drop Total amount_OC foreign;

/* Reshape in long format */

reshape long amount, i(year Name type) j(currency) string;

/* Save data */

save `Debt', replace;


/* Step 2: Construct equity data */

/* Load equity data */

tempfile Equity;

/* 2003-2006 */

forval y = $year_min/2006 {;
	if `y'==$year_min {;
		local file = "Table 24";
	};
	else {;
		local file = "Table 27";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/`file'",
		first allstring clear;

	/* Rename variables */

	rename Countryorcategory Name;
	rename Total amount;
	rename Common CommonStock;

	keep Name amount CommonStock;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(amount);

	drop total;

	/* Construct variables */

	gen int year = `y';

	/* Save data */

	if `y'!=$year_min	append using `Equity';

	save `Equity', replace;
};

/* 2007-2012 */

forval y = 2007/2012 {;
	if `y'==2007 {;
		local sheet = "Table 30";
		local cell = "A4";
	};
	else if `y'==2008 {;
		local sheet = "TB30";
		local cell = "A5";
	};
	else {;
		local sheet = "A12";
		local cell = "A5";
	};

	/* Load data */

	import excel using "`directory'/shca`y'/shc`y'r-appx",
		sheet("`sheet'") cellra(`cell') first allstring clear;

	/* Rename variables */

	rename Countryorcategory Name;
	rename Total amount;

	keep Name amount CommonStock;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(amount);

	drop total;

	/* Construct variables */

	gen int year = `y';

	/* Save data */

	append using `Equity';

	save `Equity', replace;
};

/* 2013-2020 */

forval y = 2013/$year_max {;
	if `y'==2013 {;
		local file = "appendix_tab12";
	};
	else if `y'==2014 {;
		local file = "shc14_app12";
	};
	else if `y'==2015 {;
		local file = "shc15_app12";
	};
	else {;
		local file = "shc_app12_`y'";
	};

	/* Load data */	

	import delimited using "`directory'/shca`y'/`file'",
		varn(5) case(preserve) rowr(6) stringc(_all) clear;

	/* Rename variables */

	rename Countryorregionofissuer Name;
	rename Total amount;
	cap rename Stock CommonStock;
	cap rename stock CommonStock;

	keep Name amount CommonStock;

	/* Drop total and footnotes */

	gen byte total = sum(Name=="Total");

	drop if total | missing(amount);

	drop total;

	/* Construct variables */

	gen int year = `y';

	/* Save data */

	append using `Equity';

	save `Equity', replace;
};

/* Replace small holdings */

foreach var of varlist amount CommonStock {;
	replace `var' = "0" if inlist(strtrim(`var'),"*","n.a.");
};

destring amount CommonStock, replace;

/* Split into common equity and fund shares */

expand 2, gen(duplicate);

gen byte type = 3+duplicate;

replace amount = CommonStock if type==3;
replace amount = max(0,amount-CommonStock) if type==4;

drop CommonStock duplicate;


/* Step 3: Merge debt and equity data */

append using `Debt';

/* Fix country name */

replace Name = regexr(Name,"^Saint","St.");

replace Name = regexr(Name," \([1-6]\)$","");
replace Name = regexr(Name,", mainland1?$","");
replace Name = regexr(Name,", Peoples Republic of$","");
replace Name = regexr(Name,", S.A.R.$","");

replace Name = regexr(Name,"&","and");
replace Name = regexr(Name,"Saint Eustatius","Sint Eustatius");

replace Name = "Myanmar" if Name=="Burma";
replace Name = "Cabo Verde" if Name=="Cape Verde";
replace Name = "Taiwan" if Name=="China, Republic of (Taiwan)";
replace Name = "Congo, Republic of" if Name=="Congo (Brazzaville)";
replace Name = "Cote d'Ivoire" if regexm(Name,"Cote D('|`)Ivoire");
replace Name = "Kiribati" if Name=="Kirabati";
replace Name = "South Korea" if Name=="Korea, South";
replace Name = "Kyrgyz Republic" if Name=="Kyrgyzstan";
replace Name = "Macao" if Name=="Macau";
replace Name = "Serbia" if Name=="Serbia and Montenegro";
replace Name = "Turks and Caicos Islands" if Name=="Turks and Caicos";

recast str64 Name;

/* Convert to US$ billion */

replace amount = amount/1e3;

/* Merge country code */

merge m:1 Name using Countries,
	keepusing(Counterpart Yeuro)
	keep(master match);

replace Counterpart = "_OC" if _merge==1;

drop Name _merge;

/* Eliminate double counting */

drop if currency=="OWN" & inlist(Counterpart,"IRQ","MHL","TCA","VGB");
drop if currency=="OWN" & Counterpart=="ECU" & year<=2013;
drop if currency=="OWN" & Counterpart=="ZWE" & year>=2014;
drop if currency=="OWN" & year>=Yeuro;
drop if currency=="OWN" & Counterpart=="JPN";
drop if currency=="OWN" & inlist(Counterpart,"GBR","GGY","IMN","JEY");

drop Yeuro;

/* Assign domestic currency debt */

replace currency = Counterpart if currency=="OWN";

/* Aggregate other countries */

collapse (sum) amount, by(year Counterpart type currency) fast;

/* Construct variables */

gen country = "USA";

/* Drop zeros */

drop if amount==0;

/* Label variables */

order year country Counterpart type currency;

label var year		"Year";
label var country	"Investor country";
label var type		"Asset type";
label var currency	"Currency";

label var amount	"Investment amount (US$ billion)";

label define type_label
	1 "Short-term debt"
	2 "Long-term debt"
	3 "Equity"
	4 "Fund shares";

label val type type_label;

/* Save data */

sort year country Counterpart type currency;

save Treasury, replace;

log close;
